﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{

    public class CommunityDal
    {
        public DapperHelper dapperHelper { get; set; }

        public ILog log { get; set; }

        /// <summary>
        /// 通过视图获取所有社区信息
        /// </summary>
        /// <returns></returns>
        public List<CommunityView> QueryAllCommunity()
        {
            List<CommunityView> communitys = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_COMMUNITY_INFO";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                communitys = a.Query<CommunityView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return communitys;
        }

        /// <summary>
        /// 根据社区ID查询视图信息
        /// </summary>
        /// <param name="communityId"></param>
        /// <returns></returns>
        public CommunityView QueryCommunityById(string communityId)
        {
            CommunityView communityView = new CommunityView();
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_COMMUNITY_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where(" s.new_communityId=@new_communityId and s.statecode = 0 ");
                sqlParams.Add("@new_communityId", communityId);

                communityView = a.QueryFirstOrDefault<CommunityView>(sqlTemplate.RawSql, sqlParams);
            });

            return communityView;
        }

        /// <summary>
        /// 根据社区ID查询视图信息
        /// </summary>
        /// <param name="communityId"></param>
        /// <returns></returns>
        public List<CommunityView> QueryCommunityByName(string communityName)
        {
            List<CommunityView> communityViews = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_COMMUNITY_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where(" CHARINDEX('@new_name',s.new_name)>0 and s.statecode = 0 ");
                sqlParams.Add("@new_name", communityName);

                communityViews = a.Query<CommunityView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return communityViews;
        }
        /// <summary>
        /// 根据不同条件查询社区对象,只要属性不为空就视为查询条件
        /// 查询条件为：社区ID/省份ID/市ID/区ID/CountyID/街道ID/名称(模糊搜索)
        /// 原程序 LocationQueryServiceImpl/queryCommunityByStreet
        /// </summary>
        /// <param name="communityView">社区对象</param>
        /// <returns></returns>
        public List<CommunityView> QueryCommunityByStreet(CommunityView communityView)
        {
            List<CommunityView> communityViews = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_COMMUNITY_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (communityView.new_communityId != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_communityId=@new_communityId ");
                    sqlParams.Add("@new_communityId", communityView.new_communityId);
                }
                if (communityView.new_province != null)
                {
                    sqlBuilder.Where(" s.new_province=@new_province ");
                    sqlParams.Add("@new_province", communityView.new_province);
                }
                if (!string.IsNullOrWhiteSpace(communityView.new_provinceName))
                {
                    sqlBuilder.Where(" s.new_provinceName=@new_provinceName ");
                    sqlParams.Add("@new_provinceName", communityView.new_provinceName);
                }
                if (communityView.new_city!= null)
                {
                    sqlBuilder.Where(" s.new_city=@new_city ");
                    sqlParams.Add("@new_city", communityView.new_city);
                }
                if (!string.IsNullOrWhiteSpace(communityView.new_cityName))
                {
                    sqlBuilder.Where(" s.new_cityName=@new_cityName ");
                    sqlParams.Add("@new_cityName", communityView.new_cityName);
                }
                if (communityView.new_county != null)
                {
                    sqlBuilder.Where(" s.new_county=@new_county ");
                    sqlParams.Add("@new_county", communityView.new_county);
                }
                if (!string.IsNullOrWhiteSpace(communityView.new_countyName))
                {
                    sqlBuilder.Where(" s.new_countyName=@new_countyName ");
                    sqlParams.Add("@new_countyName", communityView.new_countyName);
                }
                if (communityView.new_street != null)
                {
                    sqlBuilder.Where(" s.new_street=@new_street ");
                    sqlParams.Add("@new_street", communityView.new_street);
                }
                if (!string.IsNullOrWhiteSpace(communityView.new_name))
                {
                    //string sqll = "s.new_name like" + "'%" + communityView.new_name + "%'";
                    string sqll = $"charindex('{ communityView.new_name }',s.new_name )>0";
                    sqlBuilder.Where(sqll);
                    //sqlBuilder.Where(" s.new_name like '%@new_name%' ");
                    //sqlParams.Add("@new_name", communityView.new_name);
                }
                if (communityView.new_hospital!= null)
                {
                    sqlBuilder.Where(" s.new_hospital=@new_hospital ");
                    sqlParams.Add("@new_hospital", communityView.new_hospital);
                }
                log.Info(string.Format("CommunityDal对像中,QueryCommunityByStreet ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                communityViews = a.Query<CommunityView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return communityViews;
        }
        /// <summary>
        /// 根据不同条件查询社区数量,只要属性不为空就视为查询条件
        /// 查询条件为：社区ID/省份ID/市ID/区ID/CountyID/街道ID/名称
        /// 原程序 LocationQueryServiceImpl/queryCommunityCount
        /// </summary>
        /// <param name="communityView">社区对象</param>
        /// <returns></returns>
        public int QueryCommunityCount(CommunityView communityView)
        {
            int count = 0;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select count(*) from AIER_COMMUNITY_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (communityView.new_communityId != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_communityId=@new_communityId ");
                    sqlParams.Add("@new_communityId", communityView.new_communityId);
                }
                if (communityView.new_province != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_province=@new_province ");
                    sqlParams.Add("@new_province", communityView.new_province);
                }
                if (communityView.new_city != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_city=@new_city ");
                    sqlParams.Add("@new_city", communityView.new_city);
                }
                if (communityView.new_county != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_county=@new_county ");
                    sqlParams.Add("@new_county", communityView.new_county);
                }
                if (communityView.new_street != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_street=@new_street ");
                    sqlParams.Add("@new_street", communityView.new_street);
                }
                if (!string.IsNullOrWhiteSpace(communityView.new_name))
                {
                    sqlBuilder.Where(" s.new_name =@new_name ");
                    sqlParams.Add("@new_name", communityView.new_name);
                }
                if (communityView.new_hospital != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_hospital=@new_hospital ");
                    sqlParams.Add("@new_hospital", communityView.new_hospital);
                }
                log.Info(string.Format("CommunityDal对像中,QueryCommunityCount ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                count = a.ExecuteScalar<int>(sqlTemplate.RawSql, sqlParams);
            });
            return count;
        }

        /// <summary>
        /// 社区查询信息
        /// 原程序 ：ScreeningServiceImpl->  listCommunity(String name, String province, String county, String contact, String city, String street, String mobile, String ownerid, Integer statecode)
        /// </summary>
        /// <param name="name">社区名称</param>
        /// <param name="province">省ID</param>
        /// <param name="county">区/县ID</param>
        /// <param name="contact">联系人ID</param>
        /// <param name="city">市ID</param>
        /// <param name="street">街道ID</param>
        /// <param name="mobile">手机</param>
        /// <param name="ownerid">负责人</param>
        /// <param name="statecode">状态</param>
        /// <returns></returns>
        public IEnumerable<CommunityView> QueryCommunityByCondition(string name, string province, string county, string contact, string city, string street, string mobile, string ownerid, int statecode)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<CommunityView>>(a => {

                string sql = @"select  new_contact,new_contactName,  
                                new_cityName, new_countyName, new_provinceName, new_streetName, OwnerId, new_communityId, 
                                CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, statecode,  new_name, 
                                new_province, new_city, new_county,new_street, new_population, new_agerange, 
                                new_communitystatus, new_failurereason, new_abnormalreason, new_contact, new_mobile 
                                from new_community s /**where**/";

                var sqlTemplate = sqlBuilder.AddTemplate(sql);


                if (!string.IsNullOrEmpty(ownerid))
                {
                    sqlBuilder.Where(" s.OwnerId=@OwnerId ", new { OwnerId = ownerid });
                }

                if (!string.IsNullOrEmpty(province))
                {
                    sqlBuilder.Where(" s.new_province=@new_province ", new { new_province = province });
                }

                if (!string.IsNullOrEmpty(county))
                {
                    sqlBuilder.Where(" s.new_county=@new_county ", new { new_county = county });
                }

                if (!string.IsNullOrEmpty(contact))
                {
                    sqlBuilder.Where(" s.new_contact=@new_contact ", new { new_contact = contact });
                }

                if (!string.IsNullOrEmpty(city))
                {
                    sqlBuilder.Where(" s.new_city=@new_city ", new { new_city = city });
                }

                if (!string.IsNullOrEmpty(street))
                {
                    sqlBuilder.Where(" s.new_street=@new_street ", new { new_street = street });
                }

                if (!string.IsNullOrEmpty(mobile))
                {
                    sqlBuilder.Where(" s.new_mobile=@new_mobile ", new { new_mobile = mobile });
                }

                if (!string.IsNullOrEmpty(name))
                {
                    sqlBuilder.Where(" charindex(@new_name,s.new_name)>0 ", new { new_name = name });
                }

                sqlBuilder.Where(" s.statecode=@statecode ", new { statecode = statecode });

                log.Info(string.Format("CommunityDal对像中,QueryCommunityByCondition ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                return  a.Query<CommunityView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 社区查询信息
        /// 原程序 ：ScreeningServiceImpl->  listCommunity(String name, String province, String county, String contact, String city, String street, String mobile, String ownerid, Integer statecode)
        /// </summary>
        /// <param name="name">社区名称</param>
        /// <param name="province">省ID</param>
        /// <param name="county">区/县ID</param>
        /// <param name="contact">联系人ID</param>
        /// <param name="city">市ID</param>
        /// <param name="street">街道ID</param>
        /// <param name="mobile">手机</param>
        /// <param name="ownerid">负责人</param>
        /// <param name="statecode">状态</param>
        /// <param name="hospital">医院ID</param>
        /// <returns></returns>
        public List<CommunityView> QueryCommunityByConditionName(string name, string provinceName, string countyName, string contact, string cityName, string streetName, string mobile, string ownerid, int statecode,string hospital)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction<List<CommunityView>>(a => {

                string sql = @"select  new_contact,new_contactName,  
                                new_cityName, new_countyName, new_provinceName, new_streetName, OwnerId, new_communityId, 
                                CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, statecode,  new_name, 
                                new_province, new_city, new_county,new_street, new_population, new_agerange, 
                                new_communitystatus, new_failurereason, new_abnormalreason, new_contact, new_mobile 
                                from AIER_COMMUNITY_INFO s /**where**/";

                var sqlTemplate = sqlBuilder.AddTemplate(sql);


                if (!string.IsNullOrEmpty(ownerid))
                {
                    sqlBuilder.Where(" s.OwnerId=@OwnerId ", new { OwnerId = ownerid });
                }

                if (!string.IsNullOrEmpty(provinceName))
                {
                    sqlBuilder.Where(" s.new_provinceName=@new_province ", new { new_province = provinceName });
                }

                if (!string.IsNullOrEmpty(countyName))
                {
                    sqlBuilder.Where(" s.new_countyName=@new_county ", new { new_county = countyName });
                }

                if (!string.IsNullOrEmpty(contact))
                {
                    sqlBuilder.Where(" s.new_contact=@new_contact ", new { new_contact = contact });
                }

                if (!string.IsNullOrEmpty(cityName))
                {
                    sqlBuilder.Where(" s.new_cityName=@new_city ", new { new_city = cityName });
                }

                if (!string.IsNullOrEmpty(streetName))
                {
                    sqlBuilder.Where(" s.new_streetName=@new_street ", new { new_street = streetName });
                }

                if (!string.IsNullOrEmpty(mobile))
                {
                    sqlBuilder.Where(" s.new_mobile=@new_mobile ", new { new_mobile = mobile });
                }
                if (!string.IsNullOrWhiteSpace(hospital))
                {
                    sqlBuilder.Where(" s.new_hospital=@new_hospital ", new { new_hospital = hospital });
                }
                if (!string.IsNullOrEmpty(name))
                {
                    sqlBuilder.Where(" charindex(@new_name,s.new_name)>0 ", new { new_name = name });
                }

                sqlBuilder.Where(" s.statecode=@statecode ", new { statecode = statecode });

                log.Info(string.Format("CommunityDal对像中,QueryCommunityByCondition ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                return a.Query<CommunityView>(sqlTemplate.RawSql, sqlTemplate.Parameters).ToList();
            });
        }

        /// <summary>
        /// 社区查询信息
        /// 原程序 ：ScreeningServiceImpl->  listCommunity(String province, String county, String city, String ownerid)
        /// </summary>
        /// <param name="province">省ID</param>
        /// <param name="county">区/县ID</param>
        /// <param name="city">市ID</param>
        /// <param name="ownerid">负责人</param>
        /// <returns></returns>
        public List<CommunityView> QueryCommunityByCondition( string province, string city, string county, string ownerid,string hospital)
        {
            return QueryCommunityByConditionName(null, province, county, null, city, null, null, ownerid, 0, hospital);
        }
    }
}
